Generated code - Advanced filter usage, Adapter

Preface

This section builds upon the previous sections about filtering and shows you how to use the introduced concepts and classes in more advanced topics and situations.

Re-using filters

A predicate (expression) object is an object that is used to construct a complete WHERE clause. However, a predicate expression object can be re-used for filtering on, for example, other values for a particular field without recreating the predicate objects. Say we want to filter on a given order ID in our Typed View Invoices, by using this expression:


The value used to compare with is 11000. This is passed as a parameter value to the query for the parameter that is used to compare with Invoices.OrderID. However, if Invoices has to be re-filtered on another filter just after this call, the predicate can be re-used, the value just has to be altered. We can easily do that, because we have created a FieldCompareValue predicate. However the predicate is defined with the IPredicate interface, not with the actual classname. When you know you are going to re-use a predicate, define it with the classname:


We can now set the Value for this predicate to another value to compare with:


And the next time you specify the invoiceFilter as a filter, the value to compare the field OrderID with will be 10000.

Negative predicates

Every predicate object can be negated, i.e. will be true when the predicate itself is not true. This is accomplished by specifying 'true' for negated when using the predicate class constructors. Negated is false by default. Each predicate type knows for itself where to place the NOT statement, so this is being taken care of by the predicate itself. You can negate a predicate or predicate expression by setting its Negate property to true.

Using the native language filter construction methods, you can also negate a predicate by simply prefixing it with '!' (C#) or 'Not' (without the quotes), as shown in the following example, which filters on employeeid not equal to 2. (note that you also could have used a '!=' (C#) or '<>' (VB.NET) operator instead of the equation operator.)

Filtering on entity type

In the situation where you want to fetch entities of a particular type (and all subtypes of that particular type), if the entity type to fetch is in an inheritance hierarchy (See Concepts - Entity inheritance and relational models), it can be cumbersome to formulate the exact filter, after all the predicate classes don't supply you with a proper filter to filter on a type.

To filter on a particular type, use the following general mechanism. Say you want to limit a fetch to only BoardMember entities, which is a subtype of Manager, which is a subtype of Employee. The given filter is build in the object filter in the following example:


The method GetEntityTypeFilter(), which is available in all entities which are part of an inheritance hierarchy, produces an IPredicateExpression object which filters on the entity type you call the method on, so in our example on BoardMember.
All subtypes of the type you're filtering on will also match the filter, as they're also of the type you're filtering on. (BoardMember is-a Manager is-a Employee).

Note:

For filtering entity instances on type in memory, the DelegatePredicate is recommended with a delegate which filters using .NET native type filtering using Type.IsAssignableFrom().

Multi-entity filters

Sometimes you may want to filter on values in related entities. This is achieved by creating a RelationPredicateBucket and adding the required Relations. The RelationPredicateBucket is also used to hold additional filters; these filters are added to the PredicateExpression Property of the RelationPrediacteBucket. For example, suppose you wanted to retrieve all customers who bought a product from any supplier in France. This asks for a filter on Country, but Country is not part of the customer entity, it is part of the Supplier entity. Here's how to achieve this in code:


For VB.NET users using .NET 2.0, you could also write the predicate in this example as:

bucket.PredicateExpression.Add(SupplierFields.Country = "France")

First, the EntityCollection object is created by passing a new CustomerEntityFactory object to the constructor of a new EntityCollection. This object will store the entities that are retrieved from the persistent storage. When the collection is used to fetch entities the CustomerEntityFactory object will be used thus creating CustomerEntity instances. Next, a new RelationPredicateBucket is created to hold the RelationCollection and the PredicateExpression objects we want to use in our filter. We now add each relation in the correct order. Start with the target entity, in this case Customer, and work your way down to the entity you want to filter on. In this case the SupplierEntity. Each entity, on both sides of 'Relations' is included in the complete scope of the query, thus ProductEntity.Relations.SupplierEntityUsingSupplierID will include both Product and Supplier and thus you can filter on fields in either or both of these entities.

After the relations are added to the RelationCollection of our bucket we add the search filter to the PredicateExpression property of our bucket. We do this by adding one predicate, a FieldCompareValuePredicate, which compares Supplier.Country with the value "France". Now all the objects are ready to be used and are passed as parameters to the FetchEntityCollection method of the created DataAccessAdapter object. This will retrieve all Customer objects meeting the requirements of the filter we just defined.

There is no limit to the number of relations you can add to the RelationCollection of our bucket, however keep in mind that each added relation will result in an INNER JOIN statement (or LEFT/RIGHT JOIN when ObeyWeakRelations is set to true, see Weak relations below, if you've specified a joinhint when adding the relation to the RelationCollection by using a different Add() overload) and with a lot of relations defined, this can hurt performance. In the example, all entities in the relations are added once. If you want to filter on an entity twice, or if you use an entity twice in two, different relations, you have to specify aliasses for the entities in the relations. See Advanced filtering below for more information. Also see the section Weak relations for more information about JoinHints.

Custom filters for EntityRelations

In the section above, Multi-entity filters, it was described how relations could be specified to construct a JOIN path. The JOIN clauses themselves are determined from the relation objects, thus FK-PK compares which result in the ON clause. Sometimes it is important to specify additional predicates in this ON clause. You can do this by specifying an IPredicateExpression instance for the CustomFilter property of the EntityRelation you add to a RelationCollection. In the example below we add a custom predicate to the EntityRelation object of the relation Customer-Order and which filters on Order.ShipCountry="Mexico". It uses the example of Multi-entity filters.


Please pay special attention to the flag EntityRelation.CustomFilterReplacesOnClause. If this flag is set to true, it will make the join construction logic to use a specified CustomFilter as the ON clause instead of appending it with AND to the field relation clause.

Weak relations

The RelationCollection's default behavior is to use INNER JOINs to join the related entities together. LLBLGen Pro solves this by obeying weak relations. A weak relation is a relation that is optional. For example the entity Customer, which has a foreign key VisitingAddressID which points to the primary key AddressID of the Address entity. If VisitingAddressID is nullable, not every Customer has to have a related Address entity using the relation Customer.VisitingAddressID - Address.AddressID. If you want to read all Customer entities and you want to filter on Address.City using the Customer.VisitingAddressID - Address.AddressID relation, by default the query will do Customer INNER JOIN Address ON Customer.VisitingAddressID = Address.AddressID. This leaves out all Customer entities not having a visiting address set. If you want to filter on all Customer entities who don't have a visiting address set, you're out of luck, you need a LEFT JOIN for that.

The relation Customer.VisitingAddressID - Address.AddressID is considered weak: it is an optional relation, not every Customer entity has to have an address entity (as the field is nullable). Would VisitingAddressID not be nullable, the relation would have been considered strong. Strong relations will result in the same resultset if you use Customer LEFT JOIN Addres or if you use Customer INNER JOIN Address. A weak relation however will result in a different resultset if you use Customer LEFT JOIN Address than if you use Customer INNER JOIN Address.

To make it easier for you to automatically set the right join types for the relations, you can set the flag RelationCollection.ObeyWeakRelations to true or false. Default, the flag is false, every relation is treated as strong and all join types are INNER JOINs. If ObeyWeakRelations is set to true, the RelationCollection's routine to produce SQL from the relations will check if a relation is weak and if so, will make sure the entity which can have an optional other related entity (in our example, 'Customer') is always included in the resultset for each row. This means that for the weak relation Customer - Address, this will result in a Customer LEFT JOIN Address.

The other side of the relation, Address - Customer is also weak, as not every Address entity has to have a related Customer entity. In this case, the Address entity is the entity which can have optional related entities, so this relation will result in: Address LEFT JOIN Customer. M:N relations are always build with INNER JOINs.

It can be that a strong relation still is resulting in a LEFT JOIN. This is the case when the entity to add to the JOIN list is joined with the already joined entities by an entity which is added through a weak relation. Example: Order 1:n OrderDetails m:1 Product. Order - OrderDetails is weak, as Order doesn't have to have an OrderDetail row per se. OrderDetails - Product is strong. When ObeyWeakRelations is set to true for a typed list with these three entities (and which are using these relations), the entity Product is joined using LEFT JOIN, even though the relation is strong. This is because the OrderDetails entity is joined using LEFT JOIN, and will probably contain NULL values because of that. To keep these rows in the resultset, Product has to be joined as well. If you don't want NULL values for OrderDetails, you should have specified ObeyWeakRelations as false, which would have resulted in INNER JOINs and which would result in the rows you were interested in.

So ObeyWeakRelations resolves you from the burden to figure out which join type to use for each relation to get the resultset you want. Some developers however need finegrained control as they want INNER JOINs on some relations and LEFT/RIGHT joins on others. You can specify these join types explicitly when you add the relation to the RelationCollection, using an overload of the RelationCollection.Add() method. You specify a JoinType enum value to signal how the start entity has to be joined to the end entity of the relation. For example:


The example above illustrates the addition of the relation Customer.CustomerID - Order.CustomerID and it should be joined with a LEFT JOIN: Customer LEFT JOIN Order. By default the JoinHint for a relation is JoinHint.None. Any JoinHint other than JoinHint.None will overrule the value of ObeyWeakRelations for that particular relation.

Advanced filtering

The predicates and relations discussed up till now can do a fair amount of filtering for you. However sometimes you need more advanced filtering, for example when an entity has to be joined multiple times to the join list, using aliasses and you want to filter with one predicate on the fields of one alias and in another predicate on another alias. An example would be: get all Customers who have a visiting address in Amsterdam and a billing address in Rotterdam. Customer has two relations with Address: Customer.VisitingAddressID - Address.AddressID and Customer.BillingAddressID - Address.AddressID. Simply adding the relation CustomerEntity.Relations.AddressUsingVisitingAddressID to the RelationCollection will work, but when you add the relation CustomerEntity.Relations.AddressUsingBillingAddressID, you have two times the Address entity in the join list, how are you going to target one of them in a predicate?

The solution is to alias the entities in the relation added to the RelationCollection, and also to use the same alias in a predicate. If you omit an alias, it is considered not aliased and if you have aliased an entity in an earlier added relation to the same RelationCollection, it will be considered a different entity in the join list. So aliassing Customer to "C" in the first relation and in the second relation you do not specify an alias for Customer, you'll get 2 times a Customer entity in the join list. So use aliassing with care.

Our example of Customer and the two Address entities with the two City predicates will result in the following code. Notice the alias usage in the predicates as well.

Note:

The aliasses specified have to be valid aliasses in SQL, which means the aliasses should not contain spaces for example.